﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace LabelPrint
{
    /// <summary>
    /// 连接数据库
    /// </summary>
    public class SQLServerDataBase
    {
       public static string _connString = "server=192.168.2.239;database=JW2021;uid=sa;pwd=Jw123456";
       // public static string _connString = "server=192.168.2.239;database=JWCS3;uid=sa;pwd=Jw123456";

        
      //    public static string _connString = "server=192.168.2.99;database=admin;uid=sa;pwd=ljh6i22hc;";//设置最大连接超时时间

        public SQLServerDataBase(string connStr)
        {
            _connString = connStr;
        }

        public SQLServerDataBase()
        {

        }

        public bool OpenDataBase()
        {
            try
            {
                //创建数据库连接对象
                using (SqlConnection sqlConn = new SqlConnection(_connString))
                {
                    //打开连接
                    sqlConn.Open();
                    sqlConn.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        ///<summary>
        ///定义函数根据传入的参数，执行SQL语句获取影响的行数
        ///</summary>
        public bool ExecuteNonQuery(string sql, out int iResult)
        {
          

         
            iResult = 0;
            //创建连接数据库对象
            using (SqlConnection sqlConn = new SqlConnection(_connString))
            {
                //打开连接
                sqlConn.Open();

                //创建执行SQL语句对象
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                {
                  //  sqlCmd.CommandTimeout = 300;
                    iResult = sqlCmd.ExecuteNonQuery();
                    sqlConn.Close();
                  
                    sqlCmd.Dispose();
                    return true;
                }
            
            }
           

                
                // throw;
            
        }
        //public T ExecuteSelectT<T>(string sql,T Class)
        //{
        //    T iResult;            //创建连接数据库对象
        //    using (SqlConnection sqlConn = new SqlConnection(_connString))
        //    {
        //        //打开连接
        //        sqlConn.Open();
        //        //创建执行SQL语句对象
        //        using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
        //        { //此处可以尝试使用泛型动态声明类型
        //            iResult = (T)sqlCmd.ExecuteScalar();
        //            sqlConn.Close();
        //            sqlCmd.Dispose();
        //            //   myclass = sqlCmd.ExecuteScalar()
        //            return iResult;
        //        }
        //    }
        //}

        public Guid ExecuteSelectGuid(string sql)
        {
            Guid iResult;            //创建连接数据库对象
            using (SqlConnection sqlConn = new SqlConnection(_connString))
            {
                //打开连接
                sqlConn.Open();
                //创建执行SQL语句对象
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                { //此处可以尝试使用泛型动态声明类型
                  //  sqlCmd.CommandTimeout = 300;
                    iResult = (Guid)sqlCmd.ExecuteScalar();
                    sqlConn.Close();
                    sqlCmd.Dispose();
                    //   myclass = sqlCmd.ExecuteScalar()
                    return iResult;
                }
            }
        }


        public string ExecuteSelectStr(string sql,string connString)
        {
            //int timeout = 3100;
            string iResult = "";
            //创建连接数据库对象
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {

                //打开连接
                //  t1.Start();//启动计时器
                // sqlConn.ConnectionTimeout = timeout;
                sqlConn.Open();
                //创建执行SQL语句对象
                //  MessageBox.Show("sqlConn.Open();");
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                { //此处可以尝试使用泛型动态声明类型
                  // sqlCmd.CommandTimeout = 300;
                    if (sqlCmd.ExecuteScalar() == null)
                    {
                        iResult = "0";
                        return iResult;
                    }
                    iResult = sqlCmd.ExecuteScalar().ToString();
                    sqlConn.Close();
                    sqlCmd.Dispose();
                    //   myclass = sqlCmd.ExecuteScalar()
                    return iResult;
                }
            }
        }
        public string ExecuteSelectStr(string sql)
        {
            //int timeout = 3100;
            string iResult = "";
                    //创建连接数据库对象
                using (SqlConnection sqlConn = new SqlConnection(_connString))
                {
              
                    //打开连接
                    //  t1.Start();//启动计时器
                    // sqlConn.ConnectionTimeout = timeout;
                    sqlConn.Open();
                    //创建执行SQL语句对象
                    //  MessageBox.Show("sqlConn.Open();");
                    using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                    { //此处可以尝试使用泛型动态声明类型
                   // sqlCmd.CommandTimeout = 300;
                    if (sqlCmd.ExecuteScalar()==null)
                    {
                        iResult = "0";
                        return iResult;
                    }
                        iResult = sqlCmd.ExecuteScalar().ToString();
                        sqlConn.Close();
                        sqlCmd.Dispose();
                        //   myclass = sqlCmd.ExecuteScalar()
                        return iResult;
                    }
                }
            
        
        }

        /// <summary>
        /// 数据读取器
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteRead(string sql,ListView lv)
        {
            //int iResult;            //创建连接数据库对象
            using (SqlConnection sqlConn = new SqlConnection(_connString))
            {
                //打开连接
                sqlConn.Open();
                //创建执行SQL语句对象
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                { //此处可以尝试使用泛型动态声明类型
                   // sqlCmd.CommandTimeout = 300;
                    int i = 0;

                    SqlDataReader P_Reader = sqlCmd.ExecuteReader();
                    while (P_Reader.Read())
                    {
                       
                        ListViewItem Iv = new ListViewItem(P_Reader[i].ToString());
                        Iv.SubItems.Add(P_Reader[i].ToString());
                        i++;
                        lv.Items.Add(Iv);
                    }
                    sqlConn.Close();
                    sqlCmd.Dispose();
                    //   myclass = sqlCmd.ExecuteScalar()
                    return P_Reader;
                }
            }
        }


        /// <summary>
        /// 执行语句，返回查询结果中的第一行，第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSelect(string sql)
        {

          


                int iResult;            //创建连接数据库对象
            using (SqlConnection sqlConn = new SqlConnection(_connString))
            {
                //打开连接
                sqlConn.Open();
                //创建执行SQL语句对象
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                { //此处可以尝试使用泛型动态声明类型
                  //  sqlCmd.CommandTimeout = 300;

                    iResult = Convert.ToInt32(sqlCmd.ExecuteScalar());
                    sqlConn.Close();
                    sqlCmd.Dispose();
                    //   myclass = sqlCmd.ExecuteScalar()
                    return iResult;
                }
            } 

        }

        ///<summary>
        ///定义函数根据传入的参数，执行SQL语句获取数据集DataSet对象
        ///</summary>
        public bool ExecuteNonDataSet(string sql, out DataSet dataSet)
        {
            dataSet = null;
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(_connString))
                {
                    //创建执行SQL语句对象
                    using (SqlDataAdapter sqlData = new SqlDataAdapter(sql, sqlConn))
                    {
                        //打开连接
                        sqlConn.Open();
                        dataSet = new DataSet();
                        sqlData.Fill(dataSet, "ds");
                        sqlConn.Close();
                        sqlData.Dispose();
                        return true;
                    }
                }
            }
            catch (Exception e)
            {

                MessageBox.Show(e + "");
            }
            return true;
            //创建连接数据库对象


        }

    }
}
